import sqlite3
import datetime
import os

# 测试数据库连接和基本操作
def test_database_operations():
    print("=== 详细数据库操作测试 ===")
    
    # 1. 检查数据库文件权限
    check_file_permissions()
    
    # 2. 测试基本的插入和查询操作
    test_basic_insert_query()
    
    # 3. 检查事务处理
    test_transaction_handling()
    
    # 4. 比较当前时间与最后记录时间
    compare_timestamps()

# 检查文件权限
def check_file_permissions():
    print("\n1. 检查文件权限:")
    try:
        # 检查读权限
        with open('trade_monitor.db', 'rb') as f:
            print("  ✓ 数据库文件可读")
            
        # 检查写权限
        test_content = b"\x00"
        with open('trade_monitor.db', 'ab') as f:
            f.write(test_content)
        print("  ✓ 数据库文件可写")
        
        # 检查文件所有者
        stats = os.stat('trade_monitor.db')
        print(f"  文件大小: {os.path.getsize('trade_monitor.db') / 1024:.2f} KB")
        print(f"  最后修改时间: {datetime.datetime.fromtimestamp(stats.st_mtime)}")
        
    except Exception as e:
        print(f"  ✗ 权限检查失败: {str(e)}")

# 测试基本的插入和查询操作
def test_basic_insert_query():
    print("\n2. 测试基本的插入和查询操作:")
    test_id = f"test_{int(datetime.datetime.now().timestamp())}"
    
    try:
        # 连接数据库
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 创建临时测试表（如果不存在）
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS test_table (
                id TEXT PRIMARY KEY,
                timestamp TEXT
            )
        ''')
        conn.commit()
        print("  ✓ 成功创建测试表")
        
        # 插入测试数据
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute(
            "INSERT INTO test_table (id, timestamp) VALUES (?, ?)",
            (test_id, current_time)
        )
        conn.commit()
        print(f"  ✓ 成功插入测试数据 (ID: {test_id})")
        
        # 查询测试数据
        cursor.execute("SELECT * FROM test_table WHERE id = ?", (test_id,))
        result = cursor.fetchone()
        
        if result:
            print(f"  ✓ 查询验证成功: {result}")
        else:
            print("  ✗ 查询验证失败: 未找到插入的测试数据")
            
        # 清理测试数据
        cursor.execute("DELETE FROM test_table WHERE id = ?", (test_id,))
        conn.commit()
        print("  ✓ 成功清理测试数据")
        
    except Exception as e:
        print(f"  ✗ 基本操作测试失败: {str(e)}")
    finally:
        if conn:
            conn.close()

# 测试事务处理
def test_transaction_handling():
    print("\n3. 测试事务处理:")
    test_id = f"tx_test_{int(datetime.datetime.now().timestamp())}"
    
    try:
        # 连接数据库
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 创建临时测试表（如果不存在）
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS test_transaction_table (
                id TEXT PRIMARY KEY,
                value INTEGER
            )
        ''')
        conn.commit()
        
        # 测试事务成功情况
        try:
            cursor.execute("INSERT INTO test_transaction_table (id, value) VALUES (?, ?)", (test_id, 100))
            conn.commit()
            print("  ✓ 事务提交测试成功")
        except Exception as e:
            print(f"  ✗ 事务提交测试失败: {str(e)}")
        
        # 测试事务回滚情况
        try:
            cursor.execute("UPDATE test_transaction_table SET value = ? WHERE id = ?", (200, test_id))
            # 手动回滚
            conn.rollback()
            
            # 验证回滚是否生效
            cursor.execute("SELECT value FROM test_transaction_table WHERE id = ?", (test_id,))
            result = cursor.fetchone()
            
            if result and result[0] == 100:
                print("  ✓ 事务回滚测试成功: 值保持为100")
            else:
                print(f"  ✗ 事务回滚测试失败: 期望值100，实际值{result[0] if result else '不存在'}")
        except Exception as e:
            print(f"  ✗ 事务回滚测试失败: {str(e)}")
        
        # 清理测试数据
        cursor.execute("DELETE FROM test_transaction_table WHERE id = ?", (test_id,))
        conn.commit()
        
    except Exception as e:
        print(f"  ✗ 事务处理测试失败: {str(e)}")
    finally:
        if conn:
            conn.close()

# 比较当前时间与最后记录时间
def compare_timestamps():
    print("\n4. 比较时间戳:")
    try:
        # 连接数据库
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 获取trade_records表中最新记录的时间
        cursor.execute("SELECT MAX(timestamp) FROM trade_records")
        latest_record_time = cursor.fetchone()[0]
        print(f"  表中最新记录时间: {latest_record_time}")
        
        # 转换为datetime对象
        latest_datetime = datetime.datetime.strptime(latest_record_time, '%Y-%m-%d %H:%M:%S')
        
        # 计算与当前时间的差值
        now = datetime.datetime.now()
        time_diff = now - latest_datetime
        days_diff = time_diff.days
        
        print(f"  当前系统时间: {now.strftime('%Y-%m-%d %H:%M:%S')}")
        print(f"  时间差: {days_diff} 天")
        
        if days_diff > 7:
            print("  ✗ 警告: 超过一周没有新的交易记录")
        elif days_diff > 3:
            print("  ! 注意: 超过三天没有新的交易记录")
        else:
            print("  ✓ 交易记录时间正常")
            
    except Exception as e:
        print(f"  ✗ 时间戳比较失败: {str(e)}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    test_database_operations()